﻿using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace Think.Lenovo.Com.DataAccess
{
    public class MysqlHelper
    {
        /// <summary>
        /// 执行一条插入SQL语句,返回插入记录的主键id
        /// </summary>
        /// <param name="connection">MysqlConnection对象</param>
        /// <param name="sql">Sql语句</param>
        /// <param name="ps">Sql语句中的参数</param>
        /// <returns>返回插入记录的主键id</returns>
        public static long Insert(MySqlConnection connection, String sql, MySqlParameter[] ps)
        {
            if (connection.State.ToString() != "Open")
            {
                connection.Open();
            }
            using (MySqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(ps);
                cmd.ExecuteNonQuery();
                long result = cmd.LastInsertedId;
                connection.Close();
                return result;
            }
        }
        /// <summary>
        /// 执行一条插入SQL语句,返回插入记录的主键id
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="sql">Sql语句</param>
        /// <param name="ps">Sql语句中的参数</param>
        /// <returns>返回插入记录的主键id</returns>
        public static long Insert(String connectionString, String sql, MySqlParameter[] ps)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                return Insert(connection, sql, ps);
            }
        }
        /// <summary>
        /// 执行一条删除SQL语句,返回受影响的行数
        /// </summary>
        /// <param name="connection">MysqlConnection对象</param>
        /// <param name="sql">SQL语句,如delete from tablename where ...</param>
        /// <param name="ps">参数列表</param>
        /// <returns>执行SQL语句后,受影响的行数</returns>
        public static int Delete(MySqlConnection connection, String sql, MySqlParameter[] ps)
        {
            if (connection.State.ToString() != "Open")
            {
                connection.Open();
            }
            using (MySqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = sql;
                if (ps != null && ps.Length > 0)
                {
                    cmd.Parameters.AddRange(ps);
                }
                int result = cmd.ExecuteNonQuery();
                connection.Close();
                return result;
            }
        }
        /// <summary>
        /// 执行一条删除SQL语句,返回受影响的行数
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="sql">SQL语句,如delete from tablename where ...</param>
        /// <param name="ps">参数列表</param>
        /// <returns>执行SQL语句后,受影响的行数</returns>
        public static int Delete(String connectionString, String sql, MySqlParameter[] ps)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                return Delete(connection, sql, ps);
            }
        }
        /// <summary>
        /// 执行一条更新SQL语句,返回受影响的行数
        /// </summary>
        /// <param name="connection">MysqlConnection对象</param>
        /// <param name="sql">更新SQL语句,如Update tablename set ....</param>
        /// <param name="ps">参数列表</param>
        /// <returns></returns>
        public static int Update(MySqlConnection connection, String sql, MySqlParameter[] ps)
        {
            if (connection.State.ToString() == "Closed")
            {
                connection.Open();
            }
            using (MySqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = sql;
                if (ps != null && ps.Length > 0)
                {
                    cmd.Parameters.AddRange(ps);
                }
                int result = cmd.ExecuteNonQuery();
                connection.Close();
                return result;
            }
        }
        /// <summary>
        /// 执行一条更新SQL语句,返回受影响的行数
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="sql">更新SQL语句,如Update tablename set ....</param>
        /// <param name="ps">参数列表</param>
        /// <returns></returns>
        public static int Update(String connectionString, String sql, MySqlParameter[] ps)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                return Update(connection, sql, ps);
            }
        }
        /// <summary>
        ///执行一条查询SQL语句,返回结果集
        /// </summary>
        /// <param name="connection">MysqlConnection对象</param>
        /// <param name="sql">查询SQL语句,如Select * from tablename where ....</param>
        /// <param name="ps">参数列表</param>
        /// <returns>返回一个DataSet 对象</returns>
        public static DataSet Query(MySqlConnection connection, String sql, MySqlParameter[] ps)
        {
            if (connection.State.ToString() == "Closed")
            {
                connection.Open();
            }
            using (MySqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = sql;
                if (ps != null && ps.Length > 0)
                {
                    cmd.Parameters.AddRange(ps);
                }
                MySqlDataAdapter mda = new MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                mda.Fill(ds);
                connection.Close();
                return ds;
            }
        }
        /// <summary>
        ///执行一条查询SQL语句,返回结果集
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="sql">查询SQL语句,如Select * from tablename where ....</param>
        /// <param name="ps">参数列表</param>
        /// <returns>返回一个DataSet 对象</returns>
        public static DataSet Query(String connectionString, String sql, MySqlParameter[] ps)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                return Query(connection, sql, ps);
            }
        }
        /// <summary>
        /// 执行一条查询SQL语句
        /// </summary>
        /// <param name="connection">MysqlConnection对象</param>
        /// <param name="sql">查询SQL语句,如select * from tablename where ...</param>
        /// <returns>返回一个DataSet 对象</returns>
        public static DataSet Query(MySqlConnection connection, String sql)
        {
            return Query(connection, sql, null);
        }
        /// <summary>
        /// 执行一条查询SQL语句
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="sql">查询SQL语句,如select * from tablename where ...</param>
        /// <returns>返回一个DataSet 对象</returns>
        public static DataSet Query(String connectionString, String sql)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                return Query(connection, sql);
            }
        }
        /// <summary>
        /// 运行一个存储过程
        /// </summary>
        /// <param name="connection">MysqlConnection对象</param>
        /// <param name="procedureName">存储过程名</param>
        /// <param name="ps">参数列表</param>
        /// <returns>返回查询结果集,以及我们自定义的输出参数</returns>
        public static DataSet RunProcedure(MySqlConnection connection, String procedureName, MySqlParameter[] ps)
        {
            if (connection.State.ToString() == "Closed")
            {
                connection.Open();
            }
            using (MySqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procedureName;
                cmd.Parameters.AddRange(ps);
                if (!cmd.IsPrepared)
                    cmd.Prepare();
                MySqlDataAdapter mda = new MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                mda.Fill(ds);
                mda.Dispose();
                connection.Close();
                return ds;
            }
        }
        /// <summary>
        /// 运行一个存储过程
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="procedureName">存储过程名</param>
        /// <param name="ps">参数列表</param>
        /// <returns>返回查询结果集,以及我们自定义的输出参数</returns>
        public static DataSet RunProcedure(String connectionString, String procedureName, MySqlParameter[] ps)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                return RunProcedure(connection, procedureName, ps);
            }
        }
        
        /// <summary>
        /// 执行一条Sql语句,返回执行结果第一行第一列的值
        /// </summary>
        /// <param name="connection">MySqlConnection对象</param>
        /// <param name="sql">sql语句</param>
        /// <param name="ps">参数</param>
        /// <returns>返回执行结果的第一行第一列</returns>
        public static Object ExecuteScalar(MySqlConnection connection, String sql, MySqlParameter[] ps)
        {
            if (connection.State.ToString() == "Closed")
            {
                connection.Open();
            }
            using (MySqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = sql;
                if (ps != null && ps.Length > 0)
                {
                    cmd.Parameters.AddRange(ps);
                }
                object obj = cmd.ExecuteScalar();
                connection.Close();
                return obj;
            }
        }
        /// <summary>
        /// 执行一条Sql语句,返回执行结果的第一行第一列
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sql">sql语句</param>
        /// <param name="ps">参数列表</param>
        /// <returns>Sql语句执行结果的第一行第一列</returns>
        public static Object ExecuteScalar(String connectionString, String sql, MySqlParameter[] ps)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                return ExecuteScalar(connection, sql, ps);
            }
        }
        /// <summary>
        ///  执行一条Sql语句,返回执行结果的第一行第一列
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sql">sql语句</param>
        /// <returns>Sql语句执行结果的第一行第一列</returns>
        public static Object ExecuteScalar(String connectionString, String sql)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                return ExecuteScalar(connection, sql, null);
            }
        }

        /// <summary>
        /// 开始一个事务
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <returns>返回一个MysqlTransaction对象</returns>
        public static MySqlTransaction BeginTransaction(String connectionString)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            return connection.BeginTransaction();
        }
        /// <summary>
        /// 开始执行一个事务
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="iso">隔离级别</param>
        /// <returns>返回一个MySqlTransaction对象</returns>
        public static MySqlTransaction BeginTransaction(String connectionString, IsolationLevel iso)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlTransaction transacation = connection.BeginTransaction(iso);
            return transacation;
        }
    }
}